﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using SoufunData.DB;

namespace SoufunData
{
    public class SqlClass
    {
        public DataSet ZAdvertisingGetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tzadvertising  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            //strSql.Append(" order by insertTime desc;");
            return DbHelper.Query(strSql.ToString());
        }

        public DataSet ZAdvertisingGetMediaList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct PublicMedia from tzadvertising "); 
            return DbHelper.Query(strSql.ToString());
        }


        public DataSet ZConstructionGetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tzconstruction  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }             
            return DbHelper.Query(strSql.ToString());
        }

        public DataSet ZOpeningMonitorGetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tzopeningmonitor  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelper.Query(strSql.ToString());            
        }

        public DataSet ZProjectContractGetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tzprojectcontract  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelper.Query(strSql.ToString());
        }
         
        public DataSet ZProjectDetailGetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tzprojectdetail  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelper.Query(strSql.ToString());
        }

        public DataSet ZYushouzhengGetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tzyushouzheng  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelper.Query(strSql.ToString());
        }

        public DataSet BProjectGetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.Id,t.ProjectName,t.OpenTime,");
            strSql.Append("t.BusinessType,t.SellState,");
            strSql.Append("t.AdministratiVeregion,t.BusinessCircles,");
            strSql.Append("t.BuildBusiness,t.ManagementCompany,");
            strSql.Append("t.PropertyCompany,");
            strSql.Append("tmg.Price,tmg.SellRate,tmg.HireRate,");
            strSql.Append("tpld.CoverArea,tpld.BuildingArea,");
            strSql.Append("tpld.BusinessArea,tpld.Decorate,");
            strSql.Append("tpld.RongJiLv "); 
            strSql.Append(" from tbbasicinfo t ");
            strSql.Append("left join tbplanningdesign tpld on ");
            strSql.Append("t.ProjectName=tpld.ProjectName ");
            strSql.Append("left join tbbusinessmanage tmg on ");
            strSql.Append("t.ProjectName=tmg.ProjectName ");

            if (strWhere.Trim()!="")
            {
                strSql.Append(" where "+strWhere);
            }
            return DbHelper.Query(strSql.ToString());
        }


        public bool ExistProjectName(string projectName, string tableName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from ");
            strSql.Append(tableName);
            strSql.Append(" where ProjectName ='");
            strSql.Append(projectName);
            strSql.Append("'");
            DataSet ds = DbHelper.Query(strSql.ToString());
            if (ds==null || ds.Tables.Count==0)
            {
                return true;
            }
            int result = int.Parse(ds.Tables[0].Rows[0][0].ToString());
            return result != 0;
        }

        #region 商业表
        //*****************基本资料
        public DataSet GetBProjectBasicInfo(string projectName)
         {
             string sql = "select * from tbbasicinfo where ProjectName='"
                          + projectName + "'";
             DataSet ds = DbHelper.Query(sql);
             return ds;
         }
        //******************管理
         public DataSet GetBbusinessmanage(string projectName)
         {
             string sql = "select * from tbbusinessmanage where ProjectName='"
                          + projectName + "'";
             DataSet ds = DbHelper.Query(sql);
             return ds;
         }
        //*****************规划设计
         public DataSet GetBplanningdesign(string projectName)
         {
             string sql = "select * from tbplanningdesign where ProjectName='"
                          + projectName + "'";
             DataSet ds = DbHelper.Query(sql);
             return ds;
         }
        //****************项目总结
         public DataSet GetBsummarize(string projectName)
         {
             string sql = "select * from tbsummarize where ProjectName='"
                          + projectName + "'";
             DataSet ds = DbHelper.Query(sql);
             return ds;
         }
        //****************项目图片列表
         public DataSet GetImageList(string projectName)
         {
             string sql = "select * from tbimagefile where ProjectName='"
                          + projectName + "'";
             DataSet ds = DbHelper.Query(sql);
             return ds;
         }
        //*****************项目文件
         public string GetBfilePathFromBasic(string projectName)
         {
             string sql = "select filePath from tbbasicinfo where ProjectName='"
                          + projectName + "'";
             DataSet ds = DbHelper.Query(sql);
             if (ds==null || ds.Tables.Count==0||ds.Tables[0].Rows.Count==0)
             {
                 return null;
             }
             return ds.Tables[0].Rows[0][0].ToString();
         }
        #endregion

         //**********更新商业图片表中的列（添加图片）
        public bool UpdateImageList(string projectName, string imgType,string imgList)
        {
           string existSql = "select count(*) from tbimagefile where ProjectName='"
                          + projectName + "'";
            DataSet ds = DbHelper.Query(existSql);
            bool exist = false;
            if (ds!=null && ds.Tables.Count>0 && ds.Tables[0].Rows.Count>0)
            {
                exist = ds.Tables[0].Rows[0][0].ToString() != "0";
            }
            int result = 0;
            if (exist)
            {
                string sql = "update tbimagefile set " + imgType + "="+
                    "concat_ws(''," + imgType + ",'" +
                    imgList + "') where ProjectName ='" + projectName + "'";
                 result = DbHelper.ExecuteSql(sql);                
            }
            else
            {
                string addSql = "insert into tbimagefile(ProjectName," 
                    + imgType + ") value ('"+projectName+"','|"
                                + imgList + "')";
                result = DbHelper.ExecuteSql(addSql); 
            }
            return result > 0;
        }
        //*****更新图片数据（ 删除图片）
        public bool BImagesUpdate(string proName, string columnName, string delImage)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update tbimagefile set ");
                strSql.Append(columnName);
                strSql.Append("=Replace(");
                strSql.Append(columnName);
                strSql.Append(",'");
                strSql.Append(delImage);
                strSql.Append("','') where ProjectName='");
                strSql.Append(proName);
                strSql.Append("'");
                int result = DbHelper.ExecuteSql(strSql.ToString());
                return result > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //********更新商业基本资料表中的文件路径列（追加文件）
        public bool BusinessBasicUpdateFilePath(string projectName,string filePath)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update  tbbasicinfo set ");
            strSql.Append("FilePath=concat_ws('',FilePath,'");
            strSql.Append("|");
            strSql.Append(filePath);
            strSql.Append("') where ProjectName ='");
            strSql.Append(projectName);
            strSql.Append("' ");
            int result = DbHelper.ExecuteSql(strSql.ToString());
            return result>0;
        }

        //*****商业基本资料表中的文件路径列（ 删除图片）
        public bool BusinessBasicFilePathDelete(string proName, string delFile)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update tbbasicinfo set FilePath");
                strSql.Append("=Replace(FilePath,'");
                strSql.Append(delFile);
                strSql.Append("','') where ProjectName='");
                strSql.Append(proName);
                strSql.Append("'");
                int result = DbHelper.ExecuteSql(strSql.ToString());
                return result > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }


        //*********************获取写字楼指标数据列表
        public DataSet GetOfficeInfoList(string strWhere)
        {
            string sql = "select * from tofficedatainfo ";
            if (strWhere.Trim()!="")
            {
                sql += " where ";
                sql += strWhere;
            }
                          
            DataSet ds = DbHelper.Query(sql);
            return ds;
        }


        //********************土地表
        public bool LandSupplyExistId(string parcelId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from tlandsupply");
            strSql.Append(" where LandParcelID ='");
            strSql.Append(parcelId);
            strSql.Append("'");
            DataSet ds = DbHelper.Query(strSql.ToString());
            if (ds == null || ds.Tables.Count == 0)
            {
                return true;
            }
            int result = int.Parse(ds.Tables[0].Rows[0][0].ToString());
            return result != 0;
        }

        public bool LandTransationExistId(string parcelId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from tlandtransaction ");
            strSql.Append(" where LandParcelID ='");
            strSql.Append(parcelId);
            strSql.Append("'");
            DataSet ds = DbHelper.Query(strSql.ToString());
            if (ds == null || ds.Tables.Count == 0)
            {
                return true;
            }
            int result = int.Parse(ds.Tables[0].Rows[0][0].ToString());
            return result != 0;
        }

        public DataSet GetLandInfoList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.Ctime,t.LandParcelID,t.Landlocation,");
            strSql.Append("t.AdministratiVeregion,t.Board,t.PointsLoop,");
            strSql.Append("t.TotalAreaOfUsedLand,t.ConstructionLandArea,");
            strSql.Append("t.PlanningConstructionArea,t.GenerationOfLandArea,");
            strSql.Append("t.PlotRatio,DensityOfBuilding,");
            strSql.Append("TheCommercialScale,t.LandUse,t.TransferYear,");
            strSql.Append("t.BiddingDeposit,t.StartingPrice,t.BidIncrement,");
            strSql.Append("t.TheOriginalLandUser,t.LandStatus,");
            strSql.Append("t.IfHasSecurityHousing,t.SecurityHousingSituation,");
            strSql.Append("t.SecurityHousingArea,t.SecurityHousingType,");
            strSql.Append("t.StartTime,t.EndTime,t.AnnouncementID,");
            strSql.Append("t.TransferDocuments,t.GongdiMap,t.GongdiType,");
            strSql.Append("t.State,t.Fourrange,t.PlanedUse,");
            strSql.Append("tb.Vendee,tb.Group,tb.Price,tb.PriceOfASquare,");
            strSql.Append("tb.PriceOfAMu,tb.PremiumRate,tb.PaidTimes,");
            strSql.Append("tb.TransactionTime,tb.State ");
            strSql.Append("from tlandsupply t left join tlandtransaction tb ");
            strSql.Append("on t.LandParcelID=tb.LandParcelID ");
                        
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where ");
                strSql.Append(strWhere);
            }
            strSql.Append(" order by t.EndTime desc ");
            DataSet ds = DbHelper.Query(strSql.ToString());
            return ds;
        }

        public DataSet GetLandStatisticsResultByLaunch(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT PlanedUse,count(t.LandParcelID) as LandCount,");
            strSql.Append("sum(ConstructionLandArea) as jzydmj,sum(PlanningConstructionArea) as ghydmj,");
            strSql.Append("sum(StartingPrice)/(sum(ConstructionLandArea)*0.0015) as mmdj,");
            strSql.Append("sum(StartingPrice)*10000/sum(PlanningConstructionArea) as lmjj,");
            strSql.Append("tb.Vendee,tb.`Group`,tb.Price ");
            strSql.Append(" FROM tlandsupply t left join tlandtransaction tb on");
            strSql.Append(" t.LandParcelID=tb.LandParcelID ");
            
            if (strWhere!="")
            {
                strSql.Append(" where ");
                strSql.Append(strWhere);
            }
            DataSet ds1 = DbHelper.Query(strSql.ToString());
            if (ds1==null || ds1.Tables.Count==0)
            {
                return null;
            }
            if (ds1.Tables[0].Rows.Count==0)
            {
                return ds1;
            }
            ds1.Tables[0].Rows[0]["PlanedUse"] = "总计";
            strSql.Append(" group by PlanedUse");
            DataSet ds2 = DbHelper.Query(strSql.ToString());
            foreach (DataRow dataRow in ds2.Tables[0].Rows)
            {               
                ds1.Tables[0].Rows.Add(dataRow.ItemArray);
            }
            return ds1;
        }

        public DataSet GetLandStatisticsResultByDeal(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.LandParcelID,t.Price,");
            strSql.Append("t.TransactionTime,");
            strSql.Append("tb.PlanedUse,count(tb.LandParcelID) as LandCount,");
            strSql.Append("sum(tb.ConstructionLandArea) as jzydmj,");
            strSql.Append("sum(tb.PlanningConstructionArea) as ghydmj,");
            strSql.Append("sum(t.Price)/(sum(tb.ConstructionLandArea)*0.0015) as cjmmdj,");
            strSql.Append("sum(t.Price)*10000/sum(tb.PlanningConstructionArea) as cjlmjj,");
            strSql.Append("(sum(t.Price)/sum(tb.StartingPrice)-1) as pjyjl ");
            strSql.Append("from tlandtransaction t left join tlandsupply tb on t.LandParcelID=tb.LandParcelID ");
             
            if (strWhere != "")
            {
                strSql.Append(" where ");
                strSql.Append(strWhere);
            }
            DataSet ds1 = DbHelper.Query(strSql.ToString());
            if (ds1 == null || ds1.Tables.Count == 0)
            {
                return null;
            }
            if (ds1.Tables[0].Rows.Count == 0)
            {
                return ds1;
            }
            ds1.Tables[0].Rows[0]["PlanedUse"] = "总计";
            strSql.Append(" group by tb.PlanedUse");
            DataSet ds2 = DbHelper.Query(strSql.ToString());
            foreach (DataRow dataRow in ds2.Tables[0].Rows)
            {
                if (dataRow["cjmmdj"].ToString() == "" && dataRow["cjlmjj"].ToString() == "")
                {
                    continue;
                }
                ds1.Tables[0].Rows.Add(dataRow.ItemArray);
            }
            return ds1;
        }

        public DataSet GetLandRankings(int topValue,string strWhere,string orderPara)
        {
            StringBuilder strSql = new StringBuilder(); 
            strSql.Append("SELECT t.Vendee,t.Price,t.PriceOfASquare,t.PriceOfAMu,");
            strSql.Append("t.PremiumRate,tb.PlotRatio,tb.PlanedUse,");
            strSql.Append("tb.ConstructionLandArea,tb.PlanningConstructionArea ");
            strSql.Append(" FROM tlandtransaction t left join tlandsupply tb ");
            strSql.Append(" on t.LandParcelID = tb.LandParcelID  where tb.LandParcelID is not null ");
            if (strWhere.Trim()!="")
            {
                strSql.Append(" and ");
                strSql.Append(strWhere);
            }
            strSql.Append(" order by ");
            strSql.Append(orderPara);
            strSql.Append(" desc limit ");
            strSql.Append(topValue);
            DataSet ds = DbHelper.Query(strSql.ToString());
            return ds;
        }

        public DataSet GetLandStatisticsRankings(int topValue, string strWhere, string orderPara,string groupPara)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  count(t.LandParcelID) as dksl,t.`Group`,sum(t.Price) as cjzj,");
            strSql.Append("tb.AdministratiVeregion,tb.Board,tb.PointsLoop,");
            strSql.Append("sum(tb.ConstructionLandArea) as zjsydmj,sum(tb.PlanningConstructionArea) as zghjjmj ");
            strSql.Append(" FROM tlandtransaction t left join tlandsupply tb ");
            strSql.Append(" on t.LandParcelID = tb.LandParcelID  where tb.LandParcelID is not null ");
            strSql.Append(" and t.`Group` !='撤牌'  and t.`Group`!='流拍'");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and ");
                strSql.Append(strWhere);
            }
            strSql.Append(" group by ");
            strSql.Append(groupPara);
            strSql.Append(" order by ");
            strSql.Append(orderPara);
            strSql.Append(" desc limit ");
            strSql.Append(topValue);
 
            DataSet ds = DbHelper.Query(strSql.ToString());
            return ds;
        }
    }
}
